﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Web;
using System.Text;
using Wuqi.Webdiyer;

namespace PM.PMBasic
{
    /// <summary>
    /// Mail 的摘要说明
    /// </summary>
    public class Mail
    {
        public Mail()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        /// <summary>
        /// 插入邮件内容
        /// </summary>
        /// <param name="title">邮件标题</param>
        /// <param name="content">邮件内容</param>
        /// <param name="type">邮件类型</param>
        /// <returns>返回邮件ID</returns>
        public int Insert_Mail(string title, string content,int type)
        {
            try
            {
                SqlParameter[] MailSqlParameter ={ 
                new SqlParameter("@title",SqlDbType.NVarChar),
                new SqlParameter("@content",SqlDbType.NVarChar),
                new SqlParameter("@type",SqlDbType.TinyInt)};
                MailSqlParameter[0].Value = title;
                MailSqlParameter[1].Value = content;
                MailSqlParameter[2].Value = type;
                StringBuilder sqlstr = new StringBuilder("insert into Basic_Message(title,message,createdate,type) ");
                sqlstr.Append(" values(@title,@content,getdate(),@type)  select @@IDENTITY");
                return Int32.Parse(PM.Code.DAL.DbHelper.GetSingle(sqlstr.ToString(), MailSqlParameter).ToString());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 插入邮件关系（普通）
        /// </summary>
        /// <param name="sender">发件人</param>
        /// <param name="receiver">收件人</param>
        /// <param name="mailid">邮件ID</param>
        /// <returns>返回影响结果</returns>
        public int Insert_Mail_User1(string sender,string receiver,int mailid)
        {
            try
            {
                SqlParameter[] MailSqlParameter ={ 
                new SqlParameter("@sender",SqlDbType.NVarChar),
                new SqlParameter("@receiver",SqlDbType.NVarChar),
                new SqlParameter("@mailid",SqlDbType.Int)};
                MailSqlParameter[0].Value = sender;
                MailSqlParameter[1].Value = receiver;
                MailSqlParameter[2].Value = mailid;
                StringBuilder sqlstr = new StringBuilder("insert into Basic_UserMail(sender,receiver,msgid,instatus,outstatus,isread) ");
                sqlstr.Append(" values(@sender,@receiver,@mailid,0,0,0)");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), MailSqlParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 插入邮件关系（学院）
        /// </summary>
        /// <param name="sender">发件人</param>
        /// <param name="depart">所在学院</param>
        /// <param name="mailid">邮件ID</param>
        /// <returns>返回影响行数</returns>
        public int Insert_Mail_User2(string sender,string depart, int mailid)
        {
            try
            {
                SqlParameter[] MailSqlParameter ={ 
                new SqlParameter("@sender",SqlDbType.NVarChar),
                new SqlParameter("@depart",SqlDbType.NVarChar),
                new SqlParameter("@mailid",SqlDbType.Int)};
                MailSqlParameter[0].Value = sender;
                MailSqlParameter[1].Value = depart;
                MailSqlParameter[2].Value = mailid;
                StringBuilder sqlstr = new StringBuilder("insert into Basic_UserMail(sender,receiver,msgid,instatus,outstatus,isread) ");
                sqlstr.Append(" select @sender,tid,@mailid,0,0,0 from Sys_Account where depart=@depart");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), MailSqlParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 插入邮件关系（项目）
        /// </summary>
        /// <param name="sender">发件人</param>
        /// <param name="mailid">邮件ID</param>
        /// <returns>返回影响行数</returns>
        public int Insert_Mail_User3(string sender, int mailid)
        {
            try
            {
                SqlParameter[] MailSqlParameter ={ 
                new SqlParameter("@sender",SqlDbType.NVarChar),
                new SqlParameter("@mailid",SqlDbType.Int)};
                MailSqlParameter[0].Value = sender;
                MailSqlParameter[1].Value = mailid;
                StringBuilder sqlstr = new StringBuilder("insert into Basic_UserMail(sender,receiver,msgid,instatus,outstatus,isread) ");
                sqlstr.Append(" select @sender,tid,@mailid,0,0,0 from Sys_Account where usertype=2");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), MailSqlParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 获取未读消息数目
        /// </summary>
        /// <param name="sender">工号id</param>
        /// <returns>返回结果</returns>
        public int GetUnread(string tid)
        {
            try
            {
                StringBuilder sqlstr = new StringBuilder("select count(id) from Basic_UserMail where receiver='");
                sqlstr.Append(tid).Append("' and isread=0 and instatus=0");
                return Int32.Parse(PM.Code.DAL.DbHelper.GetSingle(sqlstr.ToString()).ToString());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 获得数据列表(分页控件)
        /// <param name="strWhere">查询条件</param>
        /// <param name="pageSize">每页显示数据数</param>
        /// <param name="pager">Aspnetpager</param>
        /// </summary>
        public DataTable GetMail(string strWhere, int pageSize, AspNetPager pager)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct(msgid),name1,title,createdate,type,depart ");
            strSql.Append(" FROM View_Messages ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by createdate desc");
            return PM.Code.DAL.DbHelper.GetPagedDataTable(strSql.ToString(), pageSize, pager);
        }
        /// <summary>
        /// 从垃圾箱中恢复单挑信息状态
        /// </summary>
        /// <param name="msgid">消息id</param>
        /// <param name="keyword">修改的字段</param>
        /// <returns>返回受影响行数</returns>
        public int GetMsgBack(string msgid,string keyword)
        {
            try
            {
                StringBuilder sqlstr = new StringBuilder("update Basic_UserMail set ");
                sqlstr.Append(keyword).Append(" where id='").Append(msgid).Append("'");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 获取一条信息
        /// </summary>
        /// <param name="msgid">消息id</param>
        /// <returns>返回datareader</returns>
        public SqlDataReader GetMessage(string msgid)
        {
            try
            {
                StringBuilder sqlstr = new StringBuilder();
                sqlstr.Append("select sender,name1,receiver,name2,title,message,createdate,type from View_Messages");
                sqlstr.Append(" where id=").Append(msgid);
                return PM.Code.DAL.DbHelper.ExecuteDataReaderBySql(sqlstr.ToString());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 标记已读
        /// </summary>
        /// <param name="msgid">消息id</param>
        /// <returns>返回datareader</returns>
        public int TagRead(string msgid)
        {
            try
            {
                StringBuilder sqlstr = new StringBuilder();
                sqlstr.Append("update Basic_UserMail set isread=1");
                sqlstr.Append(" where id=").Append(msgid);
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 更新一条消息的删除状态
        /// </summary>
        /// <param name="msgid">消息id</param>
        /// <param name="keyword">关键字</param>
        /// <returns>返回受影响的行</returns>
        public int Delete_Msg(string msgid,string keyword)
        {
            try
            {
                StringBuilder sqlstr = new StringBuilder();
                sqlstr.Append("update Basic_UserMail set ");
                sqlstr.Append(keyword).Append(" where id=").Append(msgid);
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 删除一条消息
        /// </summary>
        /// <param name="msgid">消息的msg</param>
        /// <returns>返回受影响的行</returns>
        public int Delete_Msg(string msgid)
        {
            try
            {
                List<string> sqlstr = new List<string>();
                string sqlstr1 = "delete from Basic_Message where id="+msgid;
                string sqlstr2 = "delete from Basic_UserMail where msgid="+msgid;
                sqlstr.Add(sqlstr1);
                sqlstr.Add(sqlstr2);
                return PM.Code.DAL.DbHelper.ExecuteSqlTran(sqlstr);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 删除所有无用消息（instatus和outstatus都为2）
        /// </summary>
        /// <returns>返回受影响的行</returns>
        public int Delete_Msg()
        {
            try
            {
                List<string> sqlstr = new List<string>();
                string sqlstr1 = "delete from Basic_Message where [type]=1 and id in (select msgid from Basic_UserMail where instatus=2 and outstatus=2)";
                string sqlstr2 = "delete from Basic_UserMail where msgid not in(select id from Basic_Message)";
                sqlstr.Add(sqlstr1);
                sqlstr.Add(sqlstr2);
                return PM.Code.DAL.DbHelper.ExecuteSqlTran(sqlstr);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
    }
}
